package com.yx.frame.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.json.JSONArray;
import org.json.JSONObject;

import com.yx.frame.service.ServiceError;

public class ExportExcel {
	
	public static void exportExcel(String Title, JSONArray headers, JSONArray headerMergedRegion, JSONArray columnData,  JSONArray obj, OutputStream out, ServiceError error) throws Exception {
		InputStream excelStream = null;
		ByteArrayOutputStream out1 = null;
		try{
			// 声明一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet("Sheet1");
			// 设置表格默认列宽度为15个字节
			//sheet.setDefaultColumnWidth((short) 15);
			// 设置这些样式
			//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】  
			HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象  
			HSSFCellStyle style = getStyle(workbook);                  //单元格样式对象
			HSSFRow titleRow = sheet.createRow(0);
	        //在sheet里增加合并单元格  
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, columnData.length() - 1);  
	        sheet.addMergedRegion(cra);
			HSSFCell _cell = titleRow.createCell(0);
			_cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			_cell.setCellStyle(columnTopStyle);
			HSSFRichTextString _text = new HSSFRichTextString(Title);
			_cell.setCellValue(_text);
			
			for (short i = 0; i < headers.length(); i++) {
				JSONArray headerArray = headers.getJSONArray(i);
				// 产生表格标题行
				HSSFRow row = sheet.createRow(1 + i);
				for(short j = 0; j < headerArray.length(); j++){
					String header = headerArray.getString(j);
					//创建列头对应个数的单元格
					HSSFCell cell = row.createCell(j);
					//设置列头单元格的数据类型
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);             
					//设置列头单元格样式
					cell.setCellStyle(columnTopStyle);
					//设置列头单元格的值
					HSSFRichTextString text = new HSSFRichTextString(header);
					cell.setCellValue(text);
				}
			}
			
			for(short i = 0; i < headerMergedRegion.length(); i++){
				JSONArray headerMergedReginArray = headerMergedRegion.getJSONArray(i);
				CellRangeAddress _headerMergedRegion = new CellRangeAddress(headerMergedReginArray.getInt(0),headerMergedReginArray.getInt(1),headerMergedReginArray.getInt(2),headerMergedReginArray.getInt(3));  
		        sheet.addMergedRegion(_headerMergedRegion);
			}
			
			
			//将数据设置到sheet对应的单元格中
			int index = 0;
			for(int i = 0; i < obj.length(); i++){  
				 index++;
				 JSONObject strobj = obj.getJSONObject(i);//D
				 HSSFRow row1 = sheet.createRow(i + 1 + headers.length());//创建所需的行数
				 for (short j = 0; j < columnData.length(); j++) {
					String col = columnData.getString(j);
					HSSFCell  cell = null;   //设置单元格的数据类型  
					cell = row1.createCell(j,HSSFCell.CELL_TYPE_STRING);  
	                cell.setCellValue(strobj.getString(col));                       //设置单元格的值  
	                cell.setCellStyle(style);     
				 }
			 }
			
			 //让列宽随着导出的列长自动适应  
			 for (int colNum = 0; colNum < columnData.length(); colNum++) {  
			     int columnWidth = sheet.getColumnWidth(colNum) / 256;  
			     for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {  
			         HSSFRow currentRow;  
			         //当前行未被使用过  
			         if (sheet.getRow(rowNum) == null) {  
			             currentRow = sheet.createRow(rowNum);  
			         } else {  
			             currentRow = sheet.getRow(rowNum);  
			         }  
			         if (currentRow.getCell(colNum) != null) {  
			             HSSFCell currentCell = currentRow.getCell(colNum);  
			             if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {  
			                 int length = currentCell.getStringCellValue().getBytes().length;  
			                 if (columnWidth < length) {  
			                     columnWidth = length;  
			                 }  
			             }  
			         }  
			     }  
			     if(colNum == 0){  
			    	 sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
			     }else{  
			         sheet.setColumnWidth(colNum, (columnWidth+4) * 256);  
			     }  
			 }
	
			 out1 = new ByteArrayOutputStream();  
			 workbook.write(out1);  
			 excelStream = new ByteArrayInputStream(out1.toByteArray()); 
			 byte[] buffer = new byte[1024];
			 int size = -1;
			 while ((size = excelStream.read(buffer)) != -1) {
				if (size > 0) {
					out.write(buffer, 0, size);
				}
			 }
			 out1.close();
//			 FileOutputStream out = new FileOutputStream("E:/export.xls"); 
//			 workbook.write(out); 
//			 out.close();  
		}catch(Exception e){
			e.printStackTrace();
			error.setErrorMessage(e.getMessage());
		}finally {
			if (out != null)
				out.close();
			if (excelStream != null)
				excelStream.close();
			if (out1 != null)
				out1.close();
		}
	}

	public static void exportExcel(String Title, JSONArray headers, JSONArray obj,OutputStream out) throws Exception {
		InputStream excelStream=null;
		ByteArrayOutputStream out1=null;
		try{
			// 声明一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet("Sheet1");
			// 设置表格默认列宽度为15个字节
			//sheet.setDefaultColumnWidth((short) 15);
			// 设置这些样式
			//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】  
			HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象  
			HSSFCellStyle style = getStyle(workbook);                  //单元格样式对象
			HSSFRow titleRow = sheet.createRow(0);
	        //在sheet里增加合并单元格  
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, headers.length() - 1);  
	        sheet.addMergedRegion(cra);  
			HSSFCell _cell = titleRow.createCell(0);
			_cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			_cell.setCellStyle(columnTopStyle);
			HSSFRichTextString _text = new HSSFRichTextString(Title);
			_cell.setCellValue(_text);
			
			// 产生表格标题行
			HSSFRow row = sheet.createRow(1);
			for (short i = 0; i < headers.length(); i++) {
				JSONObject headerObj = headers.getJSONObject(i);
				//创建列头对应个数的单元格
				HSSFCell cell = row.createCell(i);
				//设置列头单元格的数据类型
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);             
				//设置列头单元格样式
				cell.setCellStyle(columnTopStyle);
				//设置列头单元格的值
				//HSSFRichTextString text = new HSSFRichTextString(headers.getString(i));//L
				String key = (String)(headerObj.keys().next());
				HSSFRichTextString text = new HSSFRichTextString(key);
				cell.setCellValue(text);
			}
			//将数据设置到sheet对应的单元格中  
			int index = 0;
			for(int i=0;i<obj.length();i++){  
				 index++;
				 JSONObject strobj = obj.getJSONObject(i);//D
				 HSSFRow row1 = sheet.createRow(i+2);//创建所需的行数
				 for (short j = 0; j < headers.length(); j++) {
					JSONObject headerObj = headers.getJSONObject(j);
					String key = (String)(headerObj.keys().next());
					String col = (String)headerObj.get(key);
					HSSFCell  cell = null;   //设置单元格的数据类型  
					cell = row1.createCell(j,HSSFCell.CELL_TYPE_STRING);  
	                cell.setCellValue(strobj.getString(col));                       //设置单元格的值  
	                cell.setCellStyle(style);     
				 }
				 //D
				 /*
				 int j = 0;
				 for (Iterator iterator = strobj.keys(); iterator.hasNext();) {
					 String key = (String) iterator.next();
					 HSSFCell  cell = null;   //设置单元格的数据类型  
			         //HSSFCell cell = row.createCell(i);
		             cell = row1.createCell(j,HSSFCell.CELL_TYPE_STRING);  
		             //if(!"".equals(strobj.getString(i)) && strobj.getString(i).toString() != null){  
	                 cell.setCellValue(strobj.getString(key));                       //设置单元格的值  
		             //}  
			         cell.setCellStyle(style);                                   //设置单元格样式  
					 j++;
				 }
				 */
			 }
			 //让列宽随着导出的列长自动适应  
			 for (int colNum = 0; colNum < headers.length(); colNum++) {  
			     int columnWidth = sheet.getColumnWidth(colNum) / 256;  
			     for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {  
			         HSSFRow currentRow;  
			         //当前行未被使用过  
			         if (sheet.getRow(rowNum) == null) {  
			             currentRow = sheet.createRow(rowNum);  
			         } else {  
			             currentRow = sheet.getRow(rowNum);  
			         }  
			         if (currentRow.getCell(colNum) != null) {  
			             HSSFCell currentCell = currentRow.getCell(colNum);  
			             if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {  
			                 int length = currentCell.getStringCellValue().getBytes().length;  
			                 if (columnWidth < length) {  
			                     columnWidth = length;  
			                 }  
			             }  
			         }  
			     }  
			     if(colNum == 0){  
			         //sheet.setColumnWidth(colNum, (columnWidth-4) * 256); //L
			    	 sheet.setColumnWidth(colNum, (columnWidth+4) * 256); //D
			     }else{  
			         sheet.setColumnWidth(colNum, (columnWidth+4) * 256);  
			     }  
			 }
	
			 out1 = new ByteArrayOutputStream();  
			 workbook.write(out1);  
			 excelStream = new ByteArrayInputStream(out1.toByteArray()); 
			 byte[] buffer = new byte[1024];
			 int size = -1;
			 while ((size = excelStream.read(buffer)) != -1) {
				if (size > 0) {
					out.write(buffer, 0, size);
				}
			 }
			 out1.close();
//			 FileOutputStream out = new FileOutputStream("E:/export.xls"); 
//			 workbook.write(out); 
//			 out.close();  
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if (out != null)
				out.close();
			if (excelStream != null)
				excelStream.close();
			if (out1 != null)
				out1.close();
		}
	}
	 /*  
	    * 列头单元格样式 
	    */      
	   public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {  
         // 设置字体  
         HSSFFont font = workbook.createFont();  
         //设置字体大小  
         font.setFontHeightInPoints((short)11);  
         //字体加粗  
         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
         //设置字体名字   
         font.setFontName("Courier New");  
         //设置样式;   
         HSSFCellStyle style = workbook.createCellStyle();  
         //设置底边框;   
         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
         //设置底边框颜色;    
         style.setBottomBorderColor(HSSFColor.BLACK.index);  
         //设置左边框;     
         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
         //设置左边框颜色;   
         style.setLeftBorderColor(HSSFColor.BLACK.index);  
         //设置右边框;   
         style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
         //设置右边框颜色;   
         style.setRightBorderColor(HSSFColor.BLACK.index);  
         //设置顶边框;   
         style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
         //设置顶边框颜色;    
         style.setTopBorderColor(HSSFColor.BLACK.index);  
         //在样式用应用设置的字体;    
         style.setFont(font);  
         //设置自动换行;   
         style.setWrapText(false);  
         //设置水平对齐的样式为居中对齐;    
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
         //设置垂直对齐的样式为居中对齐;   
         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
         return style;  
	   }
	   
	   /*   
	    * 列数据信息单元格样式 
	    */    
	   public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {  
	         // 设置字体  
	         HSSFFont font = workbook.createFont();  
	         //设置字体大小  
	         //font.setFontHeightInPoints((short)10);  
	         //字体加粗  
	         //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	         //设置字体名字   
	         font.setFontName("Courier New");  
	         //设置样式;   
	         HSSFCellStyle style = workbook.createCellStyle();  
	         //设置底边框;   
	         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	         //设置底边框颜色;    
	         style.setBottomBorderColor(HSSFColor.BLACK.index);  
	         //设置左边框;     
	         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	         //设置左边框颜色;   
	         style.setLeftBorderColor(HSSFColor.BLACK.index);  
	         //设置右边框;   
	         style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	         //设置右边框颜色;   
	         style.setRightBorderColor(HSSFColor.BLACK.index);  
	         //设置顶边框;   
	         style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	         //设置顶边框颜色;    
	         style.setTopBorderColor(HSSFColor.BLACK.index);  
	         //在样式用应用设置的字体;    
	         style.setFont(font);  
	         //设置自动换行;   
	         style.setWrapText(false);  
	         //设置水平对齐的样式为居中对齐;    
	         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	         //设置垂直对齐的样式为居中对齐;   
	         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	         return style;  
	   } 
}
